package cn.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.pojo.Bill;
import cn.pojo.User;

public class ExcelUtil {
	private ExcelUtil() {
	}

	// 导出excel表格
	public static void generateExcel(List<User> list, String target) {
		// 指定目标位置
		File targetFile = new File(target);
		// 构建工作簿
		XSSFWorkbook book = new XSSFWorkbook();
		// 构建工作页
		XSSFSheet sheet = book.createSheet("userInfo");
		// 创建行
		int num = 0;
		int count = 0;
		XSSFRow row = sheet.createRow(num++);
		row.createCell(count++).setCellValue("id");
		row.createCell(count++).setCellValue("userCode");
		row.createCell(count++).setCellValue("userName");
		row.createCell(count++).setCellValue("userPassword");
		row.createCell(count++).setCellValue("gender");
		row.createCell(count++).setCellValue("birthday");
		row.createCell(count++).setCellValue("phone");
		row.createCell(count++).setCellValue("address");
		row.createCell(count++).setCellValue("userRole");
		row.createCell(count++).setCellValue("createdBy");
		row.createCell(count++).setCellValue("creationDate");
		row.createCell(count++).setCellValue("modifyBy");
		row.createCell(count++).setCellValue("modifyDate");
		row.createCell(count++).setCellValue("picpath");
		row.createCell(count++).setCellValue("resume");
		for (int i = 0; i < list.size(); i++) {
			User m = list.get(i);
			row = sheet.createRow(num++);
			count = 0;
			row.createCell(count++).setCellValue(m.getId());
			row.createCell(count++).setCellValue(m.getUserCode());
			row.createCell(count++).setCellValue(m.getUserName());
			row.createCell(count++).setCellValue(m.getUserPassword());
			row.createCell(count++).setCellValue(m.getGender() == 1 ? "男" : "女");
			row.createCell(count++).setCellValue(DateUtil.convertTimestampToString(m.getBirthday()));
			row.createCell(count++).setCellValue(m.getPhone());
			row.createCell(count++).setCellValue(m.getAddress());
			row.createCell(count++).setCellValue(m.getUserRole());
			row.createCell(count++).setCellValue(m.getCreatedBy());
			row.createCell(count++).setCellValue(DateUtil.convertTimestampToString(m.getCreationDate()));
			row.createCell(count++).setCellValue(m.getModifyBy());
			row.createCell(count++).setCellValue(DateUtil.convertTimestampToString(m.getModifyDate()));
			row.createCell(count++).setCellValue(m.getPicpath());
			row.createCell(count++).setCellValue(m.getResume());
		}
		// 写文件到目录
		OutputStream out = null;
		try {
			out = new FileOutputStream(targetFile);
			book.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

	// 通用的创建excel表的方法
	public static <T> int createExcel(Class<T> clazz, List<T> list, String path) {
		OutputStream out = null;
		XSSFWorkbook book = null;
		try {
			// 1、指定目标文件
			File target = new File(path);
			out = new FileOutputStream(target);
			// 2、创建工作簿
			book = new XSSFWorkbook();
			// 3、创建工作簿中的页，指定页的名称
			XSSFSheet sheet = book.createSheet(clazz.getSimpleName() + "Info");
			// 4、创建第1行
			int rowNum = 0;
			XSSFRow row = sheet.createRow(rowNum++);
			Field[] field = clazz.getDeclaredFields();
			// 从1开始把序列号属性去除
			for (int i = 1; i < field.length; i++) {
				String name = field[i].getName();
				row.createCell(i).setCellValue(name);
			}
			// 5、遍历集合，拼接下一行
			for (int i = 0; i < list.size(); i++) {
				// 创建下一行
				row = sheet.createRow(rowNum++);
				// 遍历所有的属性
				for (int j = 1; j < field.length; j++) {
					// 获得属性的名称
					String name = field[j].getName();
					// 把属性名首字母转大写
					name = Commons.upperCaseFirstChar(name);
					// 获得集合的元素对象
					T t = list.get(i);
					// 执行当前类的方法get方法
					Method mt = clazz.getDeclaredMethod("get" + name);
					// 获得当前getter方法的返回值类型
					Class<?> cl = mt.getReturnType();
					// 判断返回值类型，对单元格进行设置值
					String type = cl.getSimpleName();
					System.out.println(type);
					if (type.equals("String")) {
						if (mt.invoke(t) == null) {
							row.createCell(j).setCellValue("");
						} else {
							String obj = (String) (mt.invoke(t));
							row.createCell(j).setCellValue(obj);
						}
					} else if (type.equals("Long")) {
						if (mt.invoke(t) == null) {
							row.createCell(j).setCellValue("");
						} else {
							Long obj = (Long) (mt.invoke(t));
							row.createCell(j).setCellValue(obj);
						}
					} else if (type.equals("Integer")) {
						if (mt.invoke(t) == null) {
							row.createCell(j).setCellValue("");
						} else {
							Integer obj = (Integer) (mt.invoke(t));
							row.createCell(j).setCellValue(obj);
						}
					} else if (type.equals("Date")) {
						if (mt.invoke(t) == null) {
							row.createCell(j).setCellValue("");
						} else {
							Date obj = (Date) (mt.invoke(t));
							row.createCell(j).setCellValue(obj);
						}
					} else if (type.equals("Timestamp")) {
						if (mt.invoke(t) == null) {
							row.createCell(j).setCellValue("");
						} else {
							Timestamp obj = (Timestamp) (mt.invoke(t));
							row.createCell(j).setCellValue(obj);
						}
					} else {
						// 除此之外全部设置空
						row.createCell(j).setCellValue("");
					}
				}
			}
			// 6、保存文件
			book.write(out);
			System.out.println("创建表成功");
			return 1;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 7、关闭流
			if (book != null) {
				try {
					book.close();
				} catch (Exception e) {
				}
			}
		}
		return 0;
	}

	public static void generateBillExcel(List<Bill> list, String target) {
		// 指定目标位置
		File targetFile = new File(target);
		// 构建工作簿
		XSSFWorkbook book = new XSSFWorkbook();
		// 构建工作页
		XSSFSheet sheet = book.createSheet("billInfo");
		// 创建行
		int num = 0;
		int count = 0;
		XSSFRow row = sheet.createRow(num++);
		row.createCell(count++).setCellValue("id");
		row.createCell(count++).setCellValue("billCode");
		row.createCell(count++).setCellValue("productName");
		row.createCell(count++).setCellValue("productDesc");
		row.createCell(count++).setCellValue("productUnit");
		row.createCell(count++).setCellValue("productCount");
		row.createCell(count++).setCellValue("totalPrice");
		row.createCell(count++).setCellValue("isPayment");
		row.createCell(count++).setCellValue("createdBy");
		row.createCell(count++).setCellValue("creationDate");
		row.createCell(count++).setCellValue("modifyBy");
		row.createCell(count++).setCellValue("modifyDate");
		row.createCell(count++).setCellValue("providerId");
		for (int i = 0; i < list.size(); i++) {
			Bill m = list.get(i);
			row = sheet.createRow(num++);
			count = 0;
			row.createCell(count++).setCellValue(m.getId());
			row.createCell(count++).setCellValue(m.getBillCode());
			row.createCell(count++).setCellValue(m.getProductName());
			row.createCell(count++).setCellValue(m.getProductDesc());
			row.createCell(count++).setCellValue(m.getProductUnit());
			row.createCell(count++).setCellValue(m.getProductCount());
			row.createCell(count++).setCellValue(m.getTotalPrice());
			row.createCell(count++).setCellValue(m.getIsPayment());
			row.createCell(count++).setCellValue(m.getCreatedBy());
			row.createCell(count++).setCellValue(DateUtil.convertTimestampToString(m.getCreationDate()));
			row.createCell(count++).setCellValue(m.getModifyBy());
			row.createCell(count++).setCellValue(DateUtil.convertTimestampToString(m.getModifyDate()));
			row.createCell(count++).setCellValue(m.getProviderId());
		}
		// 写文件到目录
		OutputStream out = null;
		try {
			out = new FileOutputStream(targetFile);
			book.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

}
